package com.xplusplus.security.repository;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import com.xplusplus.security.domain.*;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;

/**
 * @Author: zhouweixin
 * @Description:
 * @Date: Created in 20:33 2018/5/7
 * @Modified By:
 */
@Repository
public interface UserRepository extends JpaRepository<User, String> {
    @Modifying
    @Query(value = "update User u set u.curProjectName=?1 where u.id=?2")
    void updateCurProjectName(String curProjectName, String id);

    /**
     * 通过名称模糊查询-分页
     *
     * @param name
     * @param pageable
     * @return
     */
    public Page<User> findByNameLike(String name, Pageable pageable);

    /**
     * 通过id模糊查询-分页
     *
     * @param id
     * @param pageable
     * @return
     */
    public Page<User> findByIdLike(String id, Pageable pageable);

    /**
     * 通过ic模糊查询-分页
     *
     * @param ic
     * @param pageable
     * @return
     */
    public Page<User> findByIcLike(String ic, Pageable pageable);

    /**
     * 通过id模糊查询
     *
     * @param id
     * @return
     */
    public List<User> findByIdLike(String id);

    /**
     * 通过ic模糊查询
     *
     * @param ic
     * @return
     */
    public List<User> findByIcLike(String ic);

    /**
     * 通过名称模糊查询
     *
     * @param name
     * @return
     */
    public List<User> findByNameLike(String name);

    /**
     * 通过部门和名称模糊查询-分页
     *
     * @param name
     * @param pageable
     * @return
     */
    public Page<User> findByDepartmentAndNameLike(Department department, String name, Pageable pageable);

    /**
     * 通过部门和id模糊查询-分页
     *
     * @param id
     * @param pageable
     * @return
     */
    public Page<User> findByDepartmentAndIdLike(Department department, String id, Pageable pageable);

    /**
     * 通过部门和ic模糊查询-分页
     *
     * @param ic
     * @param pageable
     * @return
     */
    public Page<User> findByDepartmentAndIcLike(Department department, String ic, Pageable pageable);

    /**
     * 通过部门简称模糊查询最大id
     *
     * @param id
     * @return
     */
    @Query(value = "select max(u.id) from User u where u.id like ?1%")
    public String findIdMaxByIdLike(String id);

    /**
     * 通过工作性质查询
     *
     * @param jobNature
     * @return
     */
    public List<User> findByJobNature(JobNature jobNature);

    public Page<User> findByJobNatureAndIdLike(JobNature jobNature, String id, Pageable pageable);

    public Page<User> findByJobNatureAndIcLike(JobNature jobNature, String ic, Pageable pageable);

    public Page<User> findByJobNatureAndNameLike(JobNature jobNature, String name, Pageable pageable);

    public Page<User> findByJobNatureAndDepartmentAndIdLike(JobNature jobNature, Department department, String id, Pageable pageable);

    public Page<User> findByJobNatureAndDepartmentAndIcLike(JobNature jobNature, Department department, String ic, Pageable pageable);

    public Page<User> findByJobNatureAndDepartmentAndNameLike(JobNature jobNature, Department department, String name, Pageable pageable);

    /**
     * 通过工作性质查询-分页
     *
     * @param jobNature
     * @param pageable
     * @return
     */
    public Page<User> findByJobNature(JobNature jobNature, Pageable pageable);

    /**
     * 通过部门和名称模糊查询
     *
     * @param department
     * @param name
     * @return
     */
    public List<User> findByDepartmentAndNameLike(Department department, String name);

    /**
     * 通过编码更新工作性质
     *
     * @param jobNature
     * @param id
     */
    @Modifying
    @Query(value = "update User u set u.jobNature=?1 where u.id=?2")
    public void updateJobNatureById(JobNature jobNature, String id);

    /**
     * 更新密码
     *
     * @param password
     */
    @Modifying
    @Query(value = "update User u set u.password=?1 where u.id=?2")
    public void updatePasswordById(String password, String id);

    /**
     * 通过部门查询
     *
     * @param department
     * @return
     */
    public List<User> findByDepartment(Department department);

    /**
     * 通过部门,工作性质,名称模糊查询
     *
     * @param department
     * @param jobNature
     * @param name
     * @return
     */
    public List<User> findByDepartmentAndJobNatureAndNameLike(Department department, JobNature jobNature, String name);

    /**
     * 通过工作性质和名称模糊查询
     *
     * @param jobNature
     * @param string
     * @return
     */
    public List<User> findByJobNatureAndNameLike(JobNature jobNature, String string);

    /**
     * 把用户从指定薪资方案里清除
     *
     * @param wage
     */
    @Modifying
    @Query(value = "update User u set u.wage=null where u.wage=?1")
    public void nullWageByWage(Wage wage);

    /**
     * 给用户分配薪资方案
     *
     * @param wage
     * @param id
     */
    @Modifying
    @Query(value = "update User u set u.wage=?1 where u.id=?2")
    public void updateWageById(Wage wage, String id);

    /**
     * 通过薪资方案查询
     *
     * @param wage
     * @return
     */
    public List<User> findByWage(Wage wage);

    /**
     * 通过薪资方案查询-分页
     *
     * @param wage
     * @return
     */
    public Page<User> findByWage(Wage wage, Pageable pageable);

    /**
     * 离职
     *
     * @param date
     * @param resignType
     * @param id
     */
    @Modifying
    @Query(value = "update User u set u.resignDate=?1, u.resignType=?2 where u.id=?3")
    public void updateResignDateAndResignType(Date date, ResignType resignType, String id);

    /**
     * 通过主键更新卡号
     *
     * @param ic
     * @param id
     */
    @Modifying
    @Query(value = "update User u set u.ic=?1 where u.id=?2")
    public void updateIcById(String ic, String id);

    /**
     * 通过ic查询
     *
     * @param ic
     * @return
     */
    public User findFirstByIc(String ic);

    /**
     * 通过性别查询数量
     *
     * @param sex
     * @return
     */
    @Query(value = "select count(u) from User u where u.sex=?1")
    public int findCountBySex(Integer sex);

    /**
     * 通过年龄段查询
     *
     * @param date1
     * @param date2
     * @return
     */
    @Query(value = "select count(u) from User u where u.bornDate>=?1 and u.bornDate<?2")
    public int findCountByBornDateGreaterThanEqualAndBornDateLessThan(Date date1, Date date2);

    /**
     * 查询员工
     *
     * @param userIds
     * @return
     */
    public List<User> findByIdIn(Collection<String> userIds);

    /**
     * 更新基本工资
     */
    @Modifying
    @Query(value = "update User u set u.baseWage=?1 where u.id in ?2")
    public void updateBaseWageByIds(double baseWage, Collection<String> ids);

    /**
     * 更新社保
     */
    @Modifying
    @Query(value = "update User u set u.socialSecuritySubsidyWage=?1 where u.id in ?2")
    public void updateSocialSecuritySubsidyWageByIds(double socialSecuritySubsidyWage, Collection<String> ids);

    /**
     * 更新基金
     */
    @Modifying
    @Query(value = "update User u set u.foundation=?1 where u.id in ?2")
    public void updateFoundationByIds(double foundation, Collection<String> ids);

    /**
     * 更新岗位津贴
     */
    @Modifying
    @Query(value = "update User u set u.postAllowance=?1 where u.id in ?2")
    public void updatePostAllowanceByIds(double postAllowance, Collection<String> ids);

    List<User> findByDepartmentAndIdLike(Department department, String s);

    List<User> findByDepartmentAndIcLike(Department department, String s);

    User findFirstByNameLike(String value);

    Page<User> findByDepartmentInAndMilitaryStatusInAndHasSecuCertInAndSexInAndBornDateAfterAndCurProjectNameLikeAndIcLike(
            List<Department> departments,
            List<MilitaryStatus> militaryStatuses,
            List<Integer> hasSecuCerts,
            List<Integer> sexes,
            Date bornDate,
            String projectName,
            String ic,
            Pageable pageable);

    Page<User> findByDepartmentInAndMilitaryStatusInAndHasSecuCertInAndSexInAndBornDateAfterAndCurProjectNameLikeAndIdLike(
            List<Department> departments,
            List<MilitaryStatus> militaryStatuses,
            List<Integer> hasSecuCerts,
            List<Integer> sexes,
            Date bornDate,
            String projectName,
            String id,
            Pageable pageable);

    Page<User> findByDepartmentInAndMilitaryStatusInAndHasSecuCertInAndSexInAndBornDateAfterAndCurProjectNameLikeAndNameLike(
            List<Department> departments,
            List<MilitaryStatus> militaryStatuses,
            List<Integer> hasSecuCerts,
            List<Integer> sexes,
            Date bornDate,
            String projectName,
            String name,
            Pageable pageable);


    @Query(value = "select u from User u where (null=?1 or u.department=?1) and (null=?2 or u.militaryStatus=?2) " +
            "and (-1=?3 or u.hasSecuCert=?3) and (-1=?4 or u.sex=?4) and ('-1'=?5 or u.bornDate>=?5) and ('-1'=?6 or u.curProjectName like ?6) " +
            "and ((0=?7 and u.name like ?8) or (1=?7 and u.id like ?8) or (2=?7 and u.ic like ?8))")
    Page<User> mulSearchByPage(Department department, MilitaryStatus militaryStatus, int hasSecuCert, int sex,
                               String date, String projectName, int type, String keyword, Pageable pageable);


    @Query(value = "select u from User u where (null=?1 or u.department=?1) and (null=?2 or u.militaryStatus=?2) " +
            "and (-1=?3 or u.hasSecuCert=?3) and (-1=?4 or u.sex=?4) and ('-1'=?5 or u.bornDate>=?5) and u.curProjectName like ?6 " +
            "and ((0=?7 and u.name like ?8) or (1=?7 and u.id like ?8) or (2=?7 and u.ic like ?8))")
    List<User> mulExportByPage(Department department, MilitaryStatus militaryStatus, int hasSecuCert, int sex,
                               String date, String projectName, int type, String keyword);
}
